﻿//=====================================================================================
// All Rights Reserved , Copyright © JYH 2013
//=====================================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Collections;
using System.Reflection;
using System.Xml.Linq;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using System.Text;

namespace JYH.Framework
{
    /// <summary>
    /// DataTable 公共帮助类
    /// </summary>
    public class DataTableHelper
    {
        #region Hashtable根据key过滤表的内容
        /// <summary>
        /// Hashtable根据key过滤表的内容
        /// </summary>
        /// <param name="dt">数据库表</param>
        /// <param name="keyField">键</param>
        /// <param name="valFiled">值</param>
        /// <returns></returns>
        public static Hashtable DataTableToHashtableByKeyValue(DataTable dt, string keyField, string valFiled)
        {
            Hashtable ht = new Hashtable();
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string key = dr[keyField].ToString();
                    ht[key] = dr[valFiled];
                }
            }
            return ht;
        }
        #endregion

        #region DataTable 转 XML
        /// <summary>
        /// DataTable 转 XML
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToXML(DataTable dt)
        {
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    System.IO.StringWriter writer = new System.IO.StringWriter();
                    dt.WriteXml(writer);
                    return writer.ToString();
                }
            }
            return String.Empty;
        }
        /// <summary>
        /// DataSet 转 XML
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static string DataSetToXML(DataSet ds)
        {
            if (ds != null)
            {
                System.IO.StringWriter writer = new System.IO.StringWriter();
                ds.WriteXml(writer);
                return writer.ToString();
            }
            return String.Empty;
        }
        #endregion

        #region DataTable 转 IList 行中是用Hashtable对象存
        /// <summary>
        /// DataTable 转 IList 行中是用Hashtable对象存
        /// </summary>
        /// <param name="dt"></param>
        /// <returns>行中是用Hashtable对象存</returns>
        public static IList<Hashtable> DataTableToArrayList(DataTable dt)
        {
            if (dt == null) return new List<Hashtable>();
            IList<Hashtable> datas = new List<Hashtable>();
            foreach (DataRow dr in dt.Rows)
            {
                Hashtable ht = DataRowToHashTable(dr);
                datas.Add(ht);
            }
            return datas;
        }
        #endregion

        #region DataTable 转 DataTableToHashtable
        /// <summary>
        /// DataTable 转 DataTableToHashtable
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static Hashtable DataTableToHashtable(DataTable dt)
        {
            Hashtable ht = new Hashtable();
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string key = dt.Columns[i].ColumnName;
                    ht[key.ToUpper()] = dr[key];
                }
            }
            return ht;
        }
        #endregion

        #region DataRow  转  HashTable
        /// <summary>
        /// DataRow  转  HashTable
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static Hashtable DataRowToHashTable(DataRow dr)
        {
            Hashtable htReturn = new Hashtable(dr.ItemArray.Length);
            foreach (DataColumn dc in dr.Table.Columns)
                htReturn.Add(dc.ColumnName, dr[dc.ColumnName]);
            return htReturn;
        }
        #endregion

        #region DataTable 转 实体类对象List
        /// <summary>
        /// DataTable 转 对象要LIST
        /// </summary>
        /// <param name="dt"></param>
        /// <returns>行中是对象的类，类的属性与数据字段一致</returns>
        public static IList DataTableToIList<T>(DataTable dt)
        {
            // 定义集合    
            IList list = new List<T>();
            // 获得此模型的类型    
            // Type type = typeof(T);
            string tempName = "";
            foreach (DataRow dr in dt.Rows)
            {
                //T t = new T();
                T obj = Activator.CreateInstance<T>();
                // 获得此模型的公共属性    
                PropertyInfo[] propertys = obj.GetType().GetProperties();

                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;

                    // 检查DataTable是否包含此列    
                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter    
                        if (!pi.CanWrite) continue;

                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            pi.SetValue(obj, value, null);
                    }
                }
                list.Add(obj);
            }
            return list;
        }
        #endregion

        #region DataTable根据条件过滤表的内容
        /// <summary>
        /// 根据条件过滤表的内容
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static DataTable GetNewDataTable(DataTable dt, string condition)
        {
            if (DataTableHelper.IsExistRows(dt))
            {
                if (condition.Trim() == "")
                {
                    return dt;
                }
                else
                {
                    DataTable newdt = new DataTable();
                    newdt = dt.Clone();
                    DataRow[] dr = dt.Select(condition);
                    for (int i = 0; i < dr.Length; i++)
                    {
                        newdt.ImportRow((DataRow)dr[i]);
                    }
                    return newdt;
                }
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 过滤DataTable重复数据！
        //// <summary>
        /// 返回执行Select distinct后的DataTable
        /// </summary>
        /// <param name="SourceTable">源数据表</param>
        /// <param name="FieldNames">字段集</param>
        /// <returns></returns>
        public static DataTable SelectDistinct(DataTable SourceTable, string[] FieldNames)
        {
            object[] lastValues;
            DataTable newTable;
            DataRow[] orderedRows;

            if (FieldNames == null || FieldNames.Length == 0)
                throw new ArgumentNullException("FieldNames");

            lastValues = new object[FieldNames.Length];
            newTable = new DataTable();

            foreach (string fieldName in FieldNames)
                newTable.Columns.Add(fieldName, SourceTable.Columns[fieldName].DataType);

            orderedRows = SourceTable.Select("", string.Join(",", FieldNames));

            foreach (DataRow row in orderedRows)
            {
                if (!fieldValuesAreEqual(lastValues, row, FieldNames))
                {
                    newTable.Rows.Add(createRowClone(row, newTable.NewRow(), FieldNames));

                    setLastValues(lastValues, row, FieldNames);
                }
            }
            return newTable;
        }
        private static DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
        {
            foreach (string field in fieldNames)
                newRow[field] = sourceRow[field];

            return newRow;
        }

        private static void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
        {
            for (int i = 0; i < fieldNames.Length; i++)
                lastValues[i] = sourceRow[fieldNames[i]];
        }

        private static bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
        {
            bool areEqual = true;

            for (int i = 0; i < fieldNames.Length; i++)
            {
                if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
                {
                    areEqual = false;
                    break;
                }
            }
            return areEqual;
        }
        #endregion

        #region 检查DataTable 是否有数据行
        /// <summary>
        /// 检查DataTable 是否有数据行
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns></returns>
        public static bool IsExistRows(DataTable dt)
        {
            if (dt != null && dt.Rows.Count > 0)
                return true;

            return false;
        }
        #endregion

        #region 排序表的视图
        /// <summary>
        /// 排序表的视图
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sorts"></param>
        /// <returns></returns>
        public static DataTable SortedTable(DataTable dt, params string[] sorts)
        {
            if (dt.Rows.Count > 0)
            {
                string tmp = "";
                for (int i = 0; i < sorts.Length; i++)
                {
                    tmp += sorts[i] + ",";
                }
                dt.DefaultView.Sort = tmp.TrimEnd(',');
            }
            return dt;
        }
        #endregion

        #region DataTable 分页
        /// <summary>
        /// Datatable 分页
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">页大小</param>
        /// <returns></returns>
        public static DataTable GetPagedTable(DataTable dt, int PageIndex, int PageSize)
        {
            if (PageIndex == 0)
                return dt;
            DataTable newdt = dt.Copy();
            newdt.Clear();
            int rowbegin = (PageIndex - 1) * PageSize;
            int rowend = PageIndex * PageSize;

            if (rowbegin >= dt.Rows.Count)
                return newdt;

            if (rowend > dt.Rows.Count)
                rowend = dt.Rows.Count;
            for (int i = rowbegin; i <= rowend - 1; i++)
            {
                DataRow newdr = newdt.NewRow();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn column in dt.Columns)
                {
                    newdr[column.ColumnName] = dr[column.ColumnName];
                }
                newdt.Rows.Add(newdr);
            }
            return newdt;
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable GetDataTableFromDictionary(Dictionary<string, string> list)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("CName");
            dt.Columns.Add("CValue");
            foreach (KeyValuePair<string, string> item in list)
            {
                dt.Rows.Add(item.Value, item.Key);
            }
            return dt;
        }
       private  static Dictionary<string, string> GetTypeChange (string FromStr,string ToStr)
        {
            XElement root = XElement.Load(Application.StartupPath+@"\TypeChange.xml");
            Dictionary<string, string> list = new Dictionary<string, string>();
            var custs = (from c in root.Elements("DbTarget")
                         where c.Attribute("From").Value.Equals(FromStr) && c.Attribute("To").Value.Equals(ToStr)
                         select c).ToList();

            foreach (XElement node in custs.Elements("Type"))
            {
               
                list.Add(node.Attribute("From").Value, node.Attribute("To").Value);
            }

            return list;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static  string GetCreateTableSQLFromDateTable(DataTable dt,string tableName,out string Msg)
        {
            Dictionary<string, string> listType = GetTypeChange("C# System Types", "SQL");

            
            string sql = string.Empty;
            Msg = string.Empty;
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = dt.TableName;
            }
             sql = "Create Table  " + tableName + " ( ";
            foreach (DataColumn dc in dt.Columns)   //循环列 
            {
                string val = "Nvarchar";
                listType.TryGetValue(dc.DataType.ToString(), out val);
                //获取创建表的SQL,具体自己再发挥 
                sql = sql + dc.ColumnName +" " + val + ", ";
            }
            sql = sql.Trim();
            sql =sql.Substring(0, sql.Length - 1) + ") ";
            return sql;

        }
        public static string GetInsertIntoSQLFromDateTable(DataTable dt, string tableName, out string Msg)
        {
            string header = string.Empty;
            string query = string.Empty;
            Msg = string.Empty;
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = dt.TableName;
            }
            header = "Insert Into [" + tableName + "] (";
            foreach (DataColumn item in dt.Columns)
            {
                header += "[" + item.ColumnName + "],";
            }
            header = header.Remove(header.Length - 1) + ") Values (";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string values = string.Empty;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    values += "'" + dt.Rows[i][j].ToString().Replace("'","''") + "',";
                }
                query += header + values.Remove(values.Length - 1) + ")\n";
            }
            return query;
        }

            /// <summary>
         /// 将DataRow赋值给model中同名属性
         /// </summary>
         /// <typeparam name="T">泛型：model的类型</typeparam>
         /// <param name="objmodel">model实例</param>
         /// <param name="dtRow">DataTable行数据</param>
         public static T TableRowToModel<T>(T objmodel, DataRow dtRow)
         {
             //获取model的类型
             Type modelType = typeof(T);
             //获取model中的属性
             PropertyInfo[] modelpropertys = modelType.GetProperties();
             //遍历model每一个属性并赋值DataRow对应的列
             foreach (PropertyInfo pi in modelpropertys)
             {
                 //获取属性名称
                 String name = pi.Name;
                 if (dtRow.Table.Columns.Contains(name))
                 {
                     //非泛型
                     if (!pi.PropertyType.IsGenericType)
                     {
                         pi.SetValue(objmodel, string.IsNullOrEmpty(dtRow[name].ToString()) ? null : Convert.ChangeType(dtRow[name], pi.PropertyType), null);
                     }
                     //泛型Nullable<>
                     else
                     {
                         Type genericTypeDefinition = pi.PropertyType.GetGenericTypeDefinition();
                         //model属性是可为null类型，进行赋null值
                         if (genericTypeDefinition == typeof(Nullable<>))
                         {
                            //返回指定可以为 null 的类型的基础类型参数
                            pi.SetValue(objmodel, string.IsNullOrEmpty(dtRow[name].ToString()) ? null : Convert.ChangeType(dtRow[name], Nullable.GetUnderlyingType(pi.PropertyType)), null);
                         }
                     }
                 }
             }
             return objmodel;
         }


        public void DataTableToCSV(DataTable dtCSV, string csvFileFullName, bool writeHeader, string delimeter)
        {
            if ((null != dtCSV) && (dtCSV.Rows.Count > 0))
            {
                //Delete the old one
                if (File.Exists(csvFileFullName))
                {
                    File.Delete(csvFileFullName);
                }

                string tmpLineText = "";

                //Write header
                if (writeHeader)
                {
                    tmpLineText = "";
                    for (int i = 0; i < dtCSV.Columns.Count; i++)
                    {
                        string tmpColumnValue = dtCSV.Columns[i].ColumnName;
                        if (tmpColumnValue.Contains(delimeter))
                        {
                            tmpColumnValue = "\"" + tmpColumnValue + "\"";
                        }

                        if (i == dtCSV.Columns.Count - 1)
                        {
                            tmpLineText += tmpColumnValue;
                        }
                        else
                        {
                            tmpLineText += tmpColumnValue + delimeter;
                        }
                    }
                    WriteFile(csvFileFullName, tmpLineText);
                }

                //Write content
                for (int j = 0; j < dtCSV.Rows.Count; j++)
                {
                    tmpLineText = "";
                    for (int k = 0; k < dtCSV.Columns.Count; k++)
                    {
                        string tmpRowValue = dtCSV.Rows[j][k].ToString();
                        if (tmpRowValue.Contains(delimeter))
                        {
                            tmpRowValue = "\"" + tmpRowValue + "\"";
                        }

                        if (k == dtCSV.Columns.Count - 1)
                        {
                            tmpLineText += tmpRowValue;
                        }
                        else
                        {
                            tmpLineText += tmpRowValue + delimeter;
                        }
                    }
                    WriteFile(csvFileFullName, tmpLineText);
                }
            }
        }

        private void WriteFile(string fileFullName, string message)
        {
            using (StreamWriter sw = new StreamWriter(fileFullName, true, Encoding.UTF8))
            {
                sw.WriteLine(message);
            }
        }

        /// <summary>
        /// 将DataTable转换成CSV文件
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="filePath">文件路径</param>
        public static void SaveCsv(DataTable dt, string filePath)
        {
            FileStream fs = null;
            StreamWriter sw = null;
            try
            {
                fs = new FileStream(filePath + dt.TableName + ".csv", FileMode.Create, FileAccess.Write);
                sw = new StreamWriter(fs, Encoding.Default);
                var data = string.Empty;
                //写出列名称
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    data += dt.Columns[i].ColumnName;
                    if (i < dt.Columns.Count - 1)
                    {
                        data += ",";
                    }
                }
                sw.WriteLine(data);
                //写出各行数据
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    data = string.Empty;
                    for (var j = 0; j < dt.Columns.Count; j++)
                    {
                        data += dt.Rows[i][j].ToString();
                        if (j < dt.Columns.Count - 1)
                        {
                            data += ",";
                        }
                    }
                    sw.WriteLine(data);
                }
            }
            catch (IOException ex)
            {
                throw new IOException(ex.Message, ex);
            }
            finally
            {
                if (sw != null) sw.Close();
                if (fs != null) fs.Close();
            }
        }
        /// <summary>
        /// DataTable转Json
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string DataTableToJson(DataTable table)
        {
            var JsonString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
                JsonString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JsonString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JsonString.Append("}");
                    }
                    else
                    {
                        JsonString.Append("},");
                    }
                }
                JsonString.Append("]");
            }
            return JsonString.ToString();
        }

    }
}
